<?php

 
 Zend_Loader::loadClass("BaseDBRows");
 Zend_Loader::loadClass("OrganizationOrganism");
 class OrganizationOrganisms extends BaseDBRows
 {
 	protected function setUp()
 	{
 		$this->_tableName = OrganizationOrganism::_tablename;
 	}	
		
	public function getOrganismByCountry($country_code='',$org_type,$organism_id = 0,$groupid=0,$othercriteria='',$groupby='',$activeOnly=1,$grouping =0,$unsuitable=0) {
		
		Zend_Loader::loadClass("OrganismAttribute");
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("Address");
		Zend_Loader::loadClass("Organismsize");
		Zend_Loader::loadClass("Organism");
		Zend_Loader::loadClass("QueryCreator");
		Zend_Loader::loadClass("OrganismGroup");
		Zend_Loader::loadClass("OrganizationAddress");
		
		$query = new QueryCreator();
		$query->addSelect(" organism.*,'' as size,'' as size_code,orgorg.org_id,org.name as supplier,
							orgorg.org_organism_id,orgorg.base_price_sale as price,
							orgorg.currency_mark,grp.organism_group_id,grp.organism_group,
							replace(organism.scientific_name,' ','-') as image ");
		$query->addFrom( OrganizationOrganism::_tablename ." as  orgorg ");
		$query->addJoin(" LEFT JOIN ". Organization::_tablename . " org on org.org_id=orgorg.org_id ");
		$query->addJoin(" LEFT JOIN ". OrganizationAddress::_tablename . " oa on oa.org_id = org.org_id");
		$query->addJoin(" LEFT JOIN ". Address::_tablename . " addr on addr.address_id = oa.address_id ");
		$query->addJoin(" LEFT JOIN ". Organism::_tablename . " organism on organism.organism_id = orgorg.organism_id ");
		//$query->addJoin(" LEFT JOIN ". OrganismAttribute::_tablename . " orgatt on orgatt.organism_id = organism.organism_id 
		//				  and orgorg.organism_attribute_id = orgatt.organism_attribute_id	" );
		//$query->addJoin(" LEFT JOIN ". Organismsize::_tablename . " orgsize on orgsize.size_code = orgatt.size_code");
		$query->addJoin(" LEFT JOIN ". OrganismGroup::_tablename . " grp on grp.organism_group_id = organism.organism_group_id ");
		
		if($unsuitable) {
			$query->addWhere(" orgorg.unsuitable = 1 ");
		} else {
			$query->addWhere(" orgorg.unsuitable = 0 ");
		}
		
		if($activeOnly) {
			$query->addWhere("ordering_status = 'Active'");
		}
		if(!empty($country_code)) 
			$query->addWhere(" addr.country_code = '". $country_code . "'" );
			
		if($groupid) {
			$query->addWhere(" grp.organism_group_id = ". $groupid );
			$query->addOrderBy(" grp.organism_group ");
		}
		
		
		if(strlen($othercriteria) > 0){
			$query->addWhere(" organism.name like  '%". $othercriteria . "%'" );
			$query->addWhereOr(" organism.scientific_name like '%". $othercriteria . "%'" );
		}
			
		$query->addWhere(" org_type = '". $org_type . "'" );
		
		if($organism_id) {
			
			$query->addWhere("organism.organism_id = ". $organism_id );
		}
		
		if(strlen($groupby) > 0){
			$query->addGroupBy($groupby);
		} else {
			//if($organism_id == 0) {
			//	$query->addGroupBy("organism.organism_id");
			//} else {
				//$query->addGroupBy(" orgatt.organism_attribute_id ");
				$query->addGroupBy(" organism.organism_id");
			//}
			
		}
			
		$query->addOrderBy("organism.name");
		$db = DBCon::instance();
		// echo $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		$data = array();
		if($grouping) {
			foreach($result as $key => $value){
				$data[$value['organism_id']][] = $value;
			}
			return $data;
			
		} else {
			return $result;
		}
		
	} 

	public function getOrganismsByOrgId($orgId, $orderingStatus="", $moid="", $group="", $searchName="", $searchAlpha="", $reload="") 
	{
		Zend_Loader::loadClass("Organization");
		Zend_Loader::loadClass("Organism");
		
		Zend_Loader::loadClass("QueryCreator");
		
		$query = new QueryCreator();
		$query->addSelect("o.name, oo.ordering_status, oo.last_buy, oo.last_sale, oo.currency_mark, oo.base_price_sale ");
		$query->addSelect("oo.reorder_level, org.name supplier_name, oo.min_order, replace(o.scientific_name,' ','-') as image  ");
		$query->addSelect("o.scientific_name, oo.org_organism_id");
		$query->addFrom($this->_tableName . " as oo ");
		$query->addJoin(" left join " . Organism::_tablename . " o on oo.organism_id = o.organism_id ");
		$query->addJoin(" left join " . Organization::_tablename . " org on oo.default_supplier_id = org.org_id ");
		$query->addWhere(" oo.org_id = " . $orgId);
		$query->addOrderBy(" o.name");
		
		// Ordering Status
		if(strlen($orderingStatus) > 0 && $orderingStatus <> 'All')
		{
			$query->addWhere(" oo.ordering_status = '". $orderingStatus."'");
		}
		
		// moid
		if(strlen($moid) > 0) {
			$query->addWhere(" o.moid = '" . $moid . "'");	
		}
		
		// group
		if(strlen($group) > 0) {
			$query->addWhere(" o.organism_group_id = " . $group);
		}
		
		// reload
		if(strlen($reload) <= 0) {
			$searchAlpha = '#';	
		}
		
		// alpha
		if(strlen($searchAlpha) > 0) {
			if ($searchAlpha == '#') {
				$query->addWhere("(ascii(lower(left(o.name,1))) < 97 or ascii(lower(left(o.name,1))) > 122)");
			} else {
				$query->addWhere("lower(left(o.name,1)) like '". $searchAlpha . "%'");
			}
		}
		
		
		// search name
		if(strlen($searchName) > 0) {
			$query->addWhere(" o.name regexp '" . $searchName . "'");	
		}
		
		$db = DBCon::instance();
		// echo  $query->createSQL();
		$result = $db->executeQuery($query->createSQL());
		return $result;
	}

	public function getOrganismByOrgId($org_id,$withAttribute=0){
		/*select orgorg.*,organism.* from organization_organisms as orgorg
			left join organisms as organism on organism.organism_id=orgorg.organism_id
			where orgorg.org_id=1 order by organism.name*/
		Zend_Loader::loadClass("Organism");
		
		if($org_id) {
			$query = new QueryCreator();
			$query->addSelect(" orgorg.*,organism.* ");
			$query->addFrom( OrganizationOrganism::_tablename ." as  orgorg ");
			$query->addJoin(" LEFT JOIN ". Organism::_tablename . " organism on organism.organism_id = orgorg.organism_id ");
			
			if($withAttribute) {
				Zend_Loader::loadClass("OrganismAttribute");	
				Zend_Loader::loadClass("Size");
				$query->addSelect(" s.size_code,s.size,orgatt.organism_attribute_id ");
				$query->addJoin(" LEFT JOIN ". OrganismAttribute::_tablename . " orgatt on orgatt.organism_id = organism.organism_id ");
				$query->addJoin(" LEFT JOIN ". Size::_tablename . " s on s.size_code = orgatt.size_code ");
				
			}
			
			$query->addWhere(" orgorg.org_id = '". $org_id . "'" );
			$query->addGroupBy("organism.name" );
			$query->addOrderBy("organism.name");
			
			
			$db = DBCon::instance();
			//echo $query->createSQL();
			$result = $db->executeQuery($query->createSQL());
			
			return $result;
		} else { return false; }
	}
 }
?>